/**
 * 
 */
package org.gov.vis.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.data.dto.ProcurementSemenDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author user
 *
 */
public class ProcurementSemenDaoImpl implements ProcurementSemenDao {

	/**
	 *Method called to either Add or Edit a ProcurementSemen
	 */
	@Override
	public void addOrEditProcurementSemen(final ProcurementSemenDto _procurementSemenDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_procurementSemenDto.getPrSemenId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_PRSEMEN);
				pStmt.setString(1, _procurementSemenDto.getCode());
				pStmt.setString(2, DU.format(_procurementSemenDto.getDoRpt(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(3, _procurementSemenDto.getFrmStation());
				pStmt.setString(4, _procurementSemenDto.getBreed());
				pStmt.setString(5, _procurementSemenDto.getBullNo());
				pStmt.setInt(6, Integer.valueOf(_procurementSemenDto.getNoStraws()));
				pStmt.setString(7, _procurementSemenDto.getDiscord());
				pStmt.setLong(8, _procurementSemenDto.getPrSemenId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_PRSEMEN);
				pStmt.setString(1, _procurementSemenDto.getCode());
				pStmt.setString(2, DU.format(_procurementSemenDto.getDoRpt(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(3, _procurementSemenDto.getFrmStation());
				pStmt.setString(4, _procurementSemenDto.getBreed());
				pStmt.setString(5, _procurementSemenDto.getBullNo());
				pStmt.setInt(6, Integer.valueOf(_procurementSemenDto.getNoStraws()));
				pStmt.setString(7, _procurementSemenDto.getDiscord());
				
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a ProcurementSemen
	 */
	@Override
	public void deleteProcurementSemen(final long _procurementSemenId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_PRSEMEN);
			pStmt.setLong(1, _procurementSemenId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of ProcurementSemens based on the passed ProcurementSemen 
	 */
	@Override
	public List<ProcurementSemenDto> getProcurementSemens(final ProcurementSemenDto _procurementSemenDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<ProcurementSemenDto> _procurementSemenList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_PRSEMEN);
			if(_procurementSemenDto.getPrSemenId() > 0){
				pStmt.setLong(1, _procurementSemenDto.getPrSemenId());
				pStmt.setLong(2, _procurementSemenDto.getPrSemenId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_procurementSemenDto.getCode() != null && !_procurementSemenDto.getCode().trim().equals("")){
				pStmt.setString(3, _procurementSemenDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_procurementSemenDto.getBullNo() != null && !_procurementSemenDto.getBullNo().trim().equals("")){
				pStmt.setString(4, _procurementSemenDto.getBullNo());
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_procurementSemenDto.getFrmStation() != null && !_procurementSemenDto.getFrmStation().trim().equals("")){
				pStmt.setString(5, _procurementSemenDto.getFrmStation());
			}
			else{
				pStmt.setString(5, "%");
			}
			if(_procurementSemenDto.getFrmStation() != null && !_procurementSemenDto.getFrmStation().trim().equals("")){
				pStmt.setString(6, _procurementSemenDto.getFrmStation());
			}
			else{
				pStmt.setString(6, "%");
			}
			rsSet = pStmt.executeQuery();
			
			_procurementSemenList = populateProcurementSemens(rsSet);
		} catch (Exception e) {
			
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _procurementSemenList;
	}//end getProcurementSemens


	/**
	 * Method called to populate the ProcurementSemen records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<ProcurementSemenDto> populateProcurementSemens(final ResultSet _rsSet)throws Exception{
		List<ProcurementSemenDto> _procurementSemenList = null;
		ProcurementSemenDto _procurementSemenDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_procurementSemenList = new ArrayList<ProcurementSemenDto>();
			}
			//initialize the ProcurementSemen
			_procurementSemenDto = new ProcurementSemenDto();
			_procurementSemenDto.setCode(_rsSet.getString(DBConstants.COL_PROCUREMENT_SEMEN_CODE));
			_procurementSemenDto.setBreed(_rsSet.getString(DBConstants.COL_PROCUREMENT_SEMEN_BREED));
			_procurementSemenDto.setBullNo(_rsSet.getString(DBConstants.COL_PROCUREMENT_SEMEN_BULLNO));
			_procurementSemenDto.setDiscord(_rsSet.getString(DBConstants.COL_PROCUREMENT_SEMEN_BULLNO));
			_procurementSemenDto.setDoRcpt(_rsSet.getDate(DBConstants.COL_PROCUREMENT_SEMEN_DORCPT));
			_procurementSemenDto.setFrmStation(_rsSet.getString(DBConstants.COL_PROCUREMENT_SEMEN_FRMSTATON));
			_procurementSemenDto.setNoStraws(_rsSet.getString(DBConstants.COL_PROCUREMENT_SEMEN_NOSTRAWS));
			_procurementSemenDto.setPrSemenId(_rsSet.getLong(DBConstants.COL_PROCUREMENT_SEMEN_ID));
			
			
			//add the current Object to the list
			_procurementSemenList.add(_procurementSemenDto);
			
		}
		return _procurementSemenList;
	}//end populateProcurementSemens

	/**
	 * Method is called to search the ProcurementSemens based on the procurementSemen.
	 */
	@Override
	public List<ProcurementSemenDto> searchProcurementSemens(final ProcurementSemenDto _procurementSemenDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<ProcurementSemenDto> _procurementSemenList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_PRSEMEN);
			if(_procurementSemenDto.getPrSemenId() > 0){
				pStmt.setLong(1, _procurementSemenDto.getPrSemenId());
				pStmt.setLong(2, _procurementSemenDto.getPrSemenId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			if(_procurementSemenDto.getCode() != null && !_procurementSemenDto.getCode().trim().equals("")){
				pStmt.setString(3, _procurementSemenDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}
			
			if(_procurementSemenDto.getBreed() != null && !_procurementSemenDto.getBreed().trim().equals("")){
				pStmt.setString(4, _procurementSemenDto.getBreed() + "%");
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_procurementSemenDto.getBullNo() != null && !_procurementSemenDto.getBullNo().trim().equals("")){
				pStmt.setString(5, _procurementSemenDto.getBullNo() + "%");
			}
			else{
				pStmt.setString(5, "%");
			}
			if(_procurementSemenDto.getFrmStation() != null && !_procurementSemenDto.getFrmStation().trim().equals("")){
				pStmt.setString(6, _procurementSemenDto.getFrmStation() + "%");
			}
			else{
				pStmt.setString(6, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_procurementSemenList = populateProcurementSemens(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _procurementSemenList;
	}//end searchProcurementSemens

}
